package cn.hpclub.server.service.goventerprice;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.util.InterfaceUtil;
import cn.hpclub.server.util.QueryHelper;
import cn.hpclub.server.util.excel.ExportUtils;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;

public class CmccOrderSuspendService {

    private static final Logger logger = LoggerFactory.getLogger(CmccOrderSuspendService.class);
    public static final CmccOrderSuspendService service = new CmccOrderSuspendService();



    public QueryHelper getQueryHelper(ConcurrentHashMap<String, String> searchInfo){
        String end_time = searchInfo.get("end_time");
        end_time = (null!=end_time)?(searchInfo.get("end_time").replaceAll(" ", "") + " 23:59:59"):"";
        String start_time = searchInfo.get("start_time");
        start_time = (null!=start_time)?(start_time.replaceAll(" ", "") + " 00:00:00"):"";
        
        
        QueryHelper  helper = new QueryHelper("select cos.*,(case cos.run_code when 'A' then '强制开机' when 'L' then '强制关机' else '未知操作' end) as _run_code_ ", "cmcc_order_suspend", "cos")
        .addCondition(searchInfo.containsKey("org_id"), "cos.org_id in("
                    + InterfaceUtil.getChlidOrgIdSql(Integer.parseInt(searchInfo.get("org_id")), Constant.MAX_ORG_LEVEL, true)+")")
        .addCondition(searchInfo.containsKey("emp_name"),
                      "cos.emp_name like ?", "%"+searchInfo.get("emp_name")+"%")
        .addCondition(searchInfo.containsKey("emp_mobile"),
                      "cos.emp_mobile = ?", searchInfo.get("emp_mobile"))
        .addCondition(searchInfo.containsKey("service_no"),
                      "cos.service_no = ?", searchInfo.get("service_no"))
        .addCondition(searchInfo.containsKey("start_time"),
                      "cos.operate_time >= ?", start_time )
        .addCondition(searchInfo.containsKey("end_time"),
                      "cos.operate_time <= ?", end_time)
        .addOrderProperty("cos.operate_time", false);
        //System.out.println(helper.getSelect()+helper.getSqlExceptSelect());
        return helper;
    }
    
    /**
     * @describe
     * @param searchInfo 查询条件参数列表
     * @return SXSSFWorkbook
     */
    public SXSSFWorkbook getExcel(ConcurrentHashMap<String, String> searchInfo){
        SXSSFWorkbook workbook = null;
        
        QueryHelper helper = getQueryHelper(searchInfo);
        String sql =helper.getSelect()+helper.getSqlExceptSelect();
        List<Record> seq = Db.find(sql);

        String[] excelHeaders = new String[]{"客户电话号码","生效天数","操作","工号","地市","区县","渠道编码","渠道名称","渠道类型","店员姓名","店员电话","是否客户经理","办理时间","备注"};
        ExportUtils exportUtils = new ExportUtils(excelHeaders);
        String[] keys = new String[]{"service_no","stop_time","_run_code_","login_no","city","country","channel_code","channel_name","channel_type","emp_name","emp_mobile","manager","operate_time","remark"};

       try {
            exportUtils.exportExcel(keys,seq,"yyyy-MM-dd HH:mm:ss",1);//此处最后一个参数表示从第一行开始填入数据（表头为第0行）
            workbook = exportUtils.getSXSSFWorkbook();
        } catch (FileNotFoundException e) {
            logger.error("CmccOrderSuspendService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an FileNotFoundException,error message:"+e.getMessage());
        } catch (IOException ex) {
            logger.error("CmccOrderSuspendService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an IOException,error message:"+ex.getMessage());
        }
        return workbook;
    }
}
